﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_get_parent_class]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fn_get_parent_class];
GO
CREATE FUNCTION [dbo].[fn_get_parent_class]
(
    @classId int
)
RETURNS TABLE
AS
RETURN
(
	WITH Class_ParentTree ( ClassID )
	AS
	(
		SELECT @classId    

		UNION ALL

		SELECT c.ClassParentID
		FROM Class_ParentTree p
			inner join dbo.UDS_Class c on p.ClassID = c.ClassID and c.ClassID <> c.ClassParentID
	)
	SELECT ClassID
	FROM Class_ParentTree
);
GO